package com.test002.util;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

public class ExcelUtil {
	/**
	 * 创建工作表，找不到路径跑出异常
	 * 
	 * @throws FileNotFoundException
	 * @throws IOException
	 */
	public static void func1() throws FileNotFoundException, IOException {
		Workbook wb = new HSSFWorkbook();
		FileOutputStream fos = new FileOutputStream("E:\\test\\.xls");
	}

	public static void func2() throws IOException {
		Workbook wb = new HSSFWorkbook();
		FileOutputStream fos = new FileOutputStream("E:\\test\\test.xls");
		Sheet sheet1 = wb.createSheet("new sheet");
		Sheet sheet2 = wb.createSheet("second sheet");
		wb.write(fos);
	}

	public static List<String[]> exportToExcel(){
		// List<String[]> 中的元素 行数组String[]为excel中的每一行 
		List<String[]> list = new ArrayList<String[]>();
		try {
		     InputStream is = new FileInputStream("E:\\test\\hehe.xls");
		     HSSFWorkbook hwk = new HSSFWorkbook(is);// 将is流实例到 一个excel流里
		     HSSFSheet sh = hwk.getSheetAt(0);// 得到book第一个工作薄sheet
		     int rows = sh.getLastRowNum()+1 - sh.getFirstRowNum();  // 总行数
		     for(int i=2; i<rows; i++){
		    	 HSSFRow row = sh.getRow(i);
		         int cols = row.getLastCellNum() - row.getFirstCellNum();  // 该行的总列数
		         String[] str = new String[cols];   // 用来存放该行每一列的值
		         for(int j = 0; j < cols; j++) {
		        	 Cell cell = row.getCell(j);
		        	 switch (cell.getCellType()) {
						case HSSFCell.CELL_TYPE_NUMERIC:
							DecimalFormat df = new DecimalFormat("0");  //避免科学计数法
							str[j] = df.format(cell.getNumericCellValue());
							break;
						case HSSFCell.CELL_TYPE_STRING:
							str[j] = cell.getStringCellValue();
						default:
							break;
						}
		         }
		         list.add(str);
		     }
		}catch(Exception e){
			e.printStackTrace();
		}finally {
			return list;
		}
	}
}
